﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using RealtorsPortal.Entities;
using System.Data.SqlClient;

namespace RealtorsPortal.Data_Access_Layer
{
    public class AccountDAL
    {
        DataAccessHelper dah = new DataAccessHelper();

        /// <summary>
        /// Check username & password when user logged
        /// </summary>
        /// <param name="user">username</param>
        /// <param name="pass">password</param>
        /// <returns>
        /// roleName: success
        /// -1: username doesn't exist
        /// -2 : wrong password
        /// </returns>
        /// 

        public DataTable GetUserTable(Account accObj) {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@UserId", SqlDbType.Int);
            para.Value = accObj.AccountId;
            paraList.Add(para);

            return dah.GetData("sp_GetUserId", paraList);
        }

        public string CheckLogin(string user, string pass)
        {
            string cmd = "select u.Password, r.RoleName from Users as u, Roles as r where u.RoleId = r.RoleId and u.Username ='" + user.ToLower() + "'";
            DataTable dt = dah.GetDataTable(cmd);
            if (dt.Rows.Count > 0)
            {
                DataRow r = dt.Rows[0];
                if (r["Password"].ToString() == pass)
                {
                    return r["RoleName"].ToString(); //return role name
                }
                else
                {
                    return "-2"; // wrong password
                }
            }
            else
            {
                return "-1"; //username doesn't exist
            }
        }



        /// <summary>
        /// Get User Id by Username
        /// </summary>
        /// <param name="user">Username</param>
        /// <returns>User Id</returns>
        public int GetIdByUsername(string user) {
            string cmd = "select UserId from Users where Username = '"+user+"'";
            return (int)dah.GetValue(cmd);
        }

        /// <summary>
        /// Get account datatable by role
        /// </summary>
        /// <param name="roleId">role id</param>
        /// <returns> Account DataTable</returns>
        public DataTable GetAccountsDataTableByRoleId(int roleId){
            string cmd = "select * from Users where RoleId = "+ roleId;
            return dah.GetDataTable(cmd);
        }

        /// <summary>
        /// Search account by username & role
        /// </summary>
        /// <param name="user">username</param>
        /// <param name="roleId">role id</param>
        /// <returns>Account DataTable</returns>
        public DataTable SearchAccountByUserName(string user, int roleId)
        {
            string cmd = "select * from Users where RoleId = "+roleId+" AND Username LIKE '%"+user+"%'";
            return dah.GetDataTable(cmd);
        }

        /// <summary>
        /// change password
        /// </summary>
        /// <param name="userId">user id</param>
        /// <param name="pass">new password</param>
        /// <returns>true if success else failed</returns>
        public bool ChangePassword(int userId, string pass)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@userId", userId));
            paraList.Add(new SqlParameter("@newpass", pass));
            return dah.ExcuteStoredNonQuery("sp_changePassword", paraList) > 0;
        }

        /// <summary>
        /// Get an Account by Id
        /// </summary>
        /// <param name="userId">user id</param>
        /// <returns>Account</returns>
        public Account GetAccountById(int userId){
            DataTable dt = dah.GetDataTable("select u.*, r.RoleName from Users as u, Roles as r where u.RoleId = r.RoleId and  UserId =" + userId);
            DataRow r = dt.Rows[0];
            Account a = new Account();
            a.AccountId = (int)r["UserId"];
            a.Address = r["Address"].ToString();
            a.Email = r["Email"].ToString();
            a.ExpDate = r["ExpDate"].ToString();
            a.Company = r["Company"].ToString();
            a.FirstName = r["FirstName"].ToString();
            a.HidedEmail = (bool)r["HidedEmail"];
            a.IsActive = (bool)r["IsActive"];
            a.LastName = r["LastName"].ToString();
            a.Password = r["Password"].ToString();
            a.PayPalAccount = r["PaypalAccount"].ToString();
            a.Phone = r["Phone"].ToString();
            a.RegDate = r["RegDate"].ToString();
            Role role = new Role();
            role.RoleId = (int)r["RoleId"];
            role.RoleName = r["RoleName"].ToString();
            a.Role = role;
            a.Username = r["Username"].ToString();
            return a;
        }

        /// <summary>
        /// Get account by username
        /// </summary>
        /// <param name="user">username</param>
        /// <returns>Account</returns>
        public Account GetAccountByUsername(string user)
        {
            DataTable dt = dah.GetDataTable("select u.*, r.* from Users as u, Roles as r where u.RoleId = r.RoleId and  Username ='" + user + "'");
            DataRow r = dt.Rows[0];
            Account a = new Account();
            a.AccountId = (int)r["UserId"];
            a.Address = r["Address"].ToString();
            a.Email = r["Email"].ToString();
            a.ExpDate = r["ExpDate"].ToString();
            a.Company = r["Company"].ToString();
            a.FirstName = r["FirstName"].ToString();
            a.HidedEmail = (bool)r["HidedEmail"];
            a.IsActive = (bool)r["IsActive"];
            a.LastName = r["LastName"].ToString();
            a.Password = r["Password"].ToString();
            a.PayPalAccount = r["PaypalAccount"].ToString();
            a.Phone = r["Phone"].ToString();
            a.RegDate = r["RegDate"].ToString();
            Role role = new Role();
            role.RoleId = (int)r["RoleId"];
            role.RoleName = r["RoleName"].ToString();
            a.Role = role;
            a.Username = r["Username"].ToString();
            return a;
        }

        /// <summary>
        /// Get Account DataTable
        /// </summary>
        /// <returns>Account DataTable</returns>
        public DataTable GetAccountsDataTable()
        {
            return dah.GetDataTable("select * from Users");
        }

        /// <summary>
        /// Get total
        /// </summary>
        /// <returns>total</returns>
        public int GetTotalAccounts()
        {
            return (int)dah.GetValue("select count(UserId) from Users");
        }
        
        /// <summary>
        /// Add an account
        /// </summary>
        /// <param name="a">Account</param>
        /// <returns>true if success else failed</returns>
        public bool AddAccount(Account a) {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@username", a.Username));
            paraList.Add(new SqlParameter("@password", a.Password));
            paraList.Add(new SqlParameter("@firstname", a.FirstName));
            paraList.Add(new SqlParameter("@lastname", a.LastName));
            paraList.Add(new SqlParameter("@email", a.Email));
            paraList.Add(new SqlParameter("@phone", a.Phone));
            paraList.Add(new SqlParameter("@company", a.Company));
            paraList.Add(new SqlParameter("@address", a.Address));
            paraList.Add(new SqlParameter("@regdate", a.RegDate));
            paraList.Add(new SqlParameter("@paypal", a.PayPalAccount));
            paraList.Add(new SqlParameter("@roleid", a.Role.RoleId));
            return dah.ExcuteStoredNonQuery("sp_insertAccount",paraList)>0;
        }

        /// <summary>
        /// Update an account
        /// </summary>
        /// <param name="a">Account</param>
        /// <returns>true if success</returns>
        public bool UpdateAccount(Account a)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@userid", a.AccountId));
            paraList.Add(new SqlParameter("@password", a.Password));
            paraList.Add(new SqlParameter("@firstname", a.FirstName));
            paraList.Add(new SqlParameter("@lastname", a.LastName));
            paraList.Add(new SqlParameter("@email", a.Email));
            paraList.Add(new SqlParameter("@phone", a.Phone));
            paraList.Add(new SqlParameter("@company", a.Company));
            paraList.Add(new SqlParameter("@address", a.Address));
            paraList.Add(new SqlParameter("@paypal", a.PayPalAccount));
            paraList.Add(new SqlParameter("@roleid", a.Role.RoleId));
            paraList.Add(new SqlParameter("@isactive",a.IsActive));
            return dah.ExcuteStoredNonQuery("sp_updateAccount", paraList) > 0;
        }

        /// <summary>
        /// Delete an account
        /// </summary>
        /// <param name="accountId">account id</param>
        /// <returns>true if success</returns>
        public bool DeleteAccount(int accountId)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@userid", accountId));
            return dah.ExcuteStoredNonQuery("sp_deleteAccount", paraList) > 0;
        }


    }
}